set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
-- **************************************************************************
--    Project Name:   够量直发
--    Job Name:       jms_dm__dm_traffic_flow_enough_direct_cnt_dt
--    Description :   够量直发 基于流量流向中心到中心的汇总数据聚合
--    Author :        郭瑞玲
--    date：          2022/08/03
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_tab_transfer_mail_waybill_detail_dt << [
--       jms_dm.dm_traffic_flow_network_dt,
--       jms_dim.dim_yl_tmsnew_tms_road_section_base,
--       jms_dim.dim_yl_tms_route_base,
--       jms_dim.dim_tms_mainline_vehicleloading
--    ]
-- **************************************************************************
--    逻辑变更：2022/08/12
--             体积逻辑修改 维表改用dim层
--             2022/12/30
-- **************************************************************************

with tab_traffic_flow as (
    select inputtime,                                                                           --日期
           send_agent_code,--始发代理区
           send_agent,
           send_distributecode,                                                                 --始发中心
           send_distribute,
           receiver_distributecode,
           receiver_distribute,
           receiver_agent_code,
           receiver_agent,
           sum(package_number)                                         as package_number,       --正向件数
           sum(package_total_weight)                                   as package_total_weight, --正向重量
           sum(cast(package_total_volume / 1000000 as decimal(16, 2))) as package_total_volume  --正向体积
    from jms_dm.dm_traffic_flow_network_new_dt t   --流量流向汇总表
             inner join jms_dim.dim_network_whole_massage net1
                        on t.send_distributecode = net1.code and net1.network_type = 4
             inner join jms_dim.dim_network_whole_massage net2
                        on t.receiver_distributecode = net2.code and net2.network_type = 4
    where dt = '{{ execution_date | cst_ds }}'
      and count_type = 6 --6中心到中心
    group by inputtime,           --日期
             send_agent_code,--始发代理区
             send_agent,
             send_distributecode, --始发中心
             send_distribute,
             receiver_distributecode,
             receiver_distribute,
             receiver_agent_code,
             receiver_agent
),
 tms_road_section as (
         select *
         from jms_dim.dim_yl_tmsnew_tms_road_section_base
         where is_enable = 1
     ),
     tms_route as
         (
             select start_code, end_code, route_name, max(next_code) next_code
             from jms_dim.dim_yl_tms_route_base_dt
             where  status = 1 and dt = '{{ execution_date | cst_ds }}'
               and route_type = 1
             group by start_code, end_code, route_name
         )

insert overwrite table jms_dm.dm_traffic_flow_enough_direct_cnt_dt partition(dt)
select inputtime,                                                                          --日期
       send_agent_code,--始发代理区
       send_agent,
       send_distributecode,                                                                --始发中心
       send_distribute,
       receiver_distributecode,
       receiver_distribute,
       receiver_agent_code,
       receiver_agent,
       package_number,                                                                     --正向件数
       package_total_weight,                                                               --正向重量
       package_number*vehiclevolume as package_total_volume,                                                           --正向体积,
       mileage,                                                                            --规划里程
       vehicle_count,                                                                      --核载票数
       route_name,--路由名称
       cast(package_number / vehicle_count as decimal(10, 4)) as forcast_load_rt,--预估装载率
       case
           when next_code is not null and cast(package_number / vehicle_count as decimal(10, 4)) >= 0.65 then 1
           when next_code is not null and cast(package_number / vehicle_count as decimal(10, 4)) < 0.65 then 0
           else NULL
           end                                                as is_can_enough_drict_send, --是否够量直发
       case
           when next_code is not null and package_total_volume >= 30 then 1
           when next_code is not null and package_total_volume < 30 then 0
           else NULL
           end                                                as is_can_stop_drict_send,   --是否够量直发
       inputtime                                              as dt
from (
         select a.inputtime,                                           --日期
                a.send_agent_code,--始发代理区
                a.send_agent,
                a.send_distributecode,                                 --始发中心
                a.send_distribute,
                a.receiver_distributecode,
                a.receiver_distribute,
                a.receiver_agent_code,
                a.receiver_agent,
                a.package_number,                                      --正向件数
                a.package_total_weight,                                --正向重量
                a.package_total_volume,                                --正向体积,
                b.mileage,                                             --规划里程
--case when b.mileage>1000 then 1
--     when b.mileage<=1000 then  0
--     else 9 --b.mileage 是空值
--     end as is_gre_1000KM, --是否大于1000KM 用于取核载
                nvl(c.vehiclecount, cc.vehiclecount) as vehicle_count, --核载票数
                nvl(c.averagevolume, cc.averagevolume) as vehiclevolume,--核载平均体积
                c.vehicletype                        as vehicle_type,
                r.route_name,--路由名称
                r.next_code,
                min(c.vehiclecount)                     over(partition by inputtime,send_distributecode) min_vehiclecount --若里程为空则取值最小的那个件量
         from tab_traffic_flow a --流向流向汇总
                  left join tms_road_section b --路段管理表 取规划里程
                            on a.send_distributecode = b.start_code and a.receiver_distributecode = b.end_code
                  left join tms_route r --路由表 取路段名称
                            on a.send_distributecode = r.start_code and a.receiver_distributecode = r.end_code
                  left join jms_dim.dim_tms_mainline_vehicleloading c ---干线荷载维护表 取核载体积和核载票数
                            on (a.send_distributecode = c.distributecode and b.mileage > 700 and
                                c.vehicletype = '16.5米') --会重复
                  left join jms_dim.dim_tms_mainline_vehicleloading cc ---干线荷载维护表 取核载体积和核载票数
                            on (a.send_distributecode = cc.distributecode and b.mileage <= 700 and
                                cc.vehicletype = '9.6米') --会重复
     ) t

distribute by pmod(hash(rand()), 3);